# -*- coding: utf-8 -*-

###########################################################################
#
#   Copyright (c) 2020  Bresee, Inc. All Rights Reserved.
#
# Licensed under the Apache License, Version 2.0 (the "License"
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
###########################################################################


import time
import utils.pgsql.base.pgconn as pgconn

"""
Area Table 地区表
add 
delete
modify
...
"""
_tblName = "tbl_area"

_DB_SCRIPT = """
CREATE TABLE IF NOT EXISTS {} (
                "areaId"  SERIAL PRIMARY KEY ,    
                "caseId" INTEGER NOT NULL default 0,      
                "areaName"  VARCHAR(260) NOT NULL default '' unique,   
                "showState" VARCHAR(64) NOT NULL default '1',          
                "imgPath" VARCHAR(260) NOT NULL default '',    
                "description" VARCHAR(260) NOT NULL default '', 
                "createTime" VARCHAR(32) NOT NULL default '',
                "updateTime" VARCHAR(32) NOT NULL default ''
                );

""".format(
    _tblName
)


class PgArea:
    def __init__(self):
        self._pgConn = pgconn.pgMainConn

    def __del__(self):
        pass

    def createTbl(self):
        """
        create
        :return:
        """
        return self._pgConn.exec(_DB_SCRIPT)

        # 增加记录, 返回id

    def addArea(self, dic) -> str:
        dic["createTime"] = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        sql_key = '\"' + '\", \"'.join(dic.keys()) + '\"'
        sql_val = ''
        for key in dic:
            if type(dic[key]) is str:
                sql_val = sql_val + '\'' + dic[key] + '\','
            else:
                sql_val = sql_val + str(dic[key]) + ' ,'

        
        sql = f"""insert into {_tblName} ({sql_key}) values ({sql_val[:-1]}) RETURNING "{"areaId"}" """
        
        return pgconn.pgMainConn.exec_fetchone(sql)

    #  
    def modify(self, ids, dic) -> bool:
        dic["updateTime"] = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())

        sql_key = ''
        for key in dic:
            if type(dic[key]) is str:
                sql_key = sql_key + '\"' + key + '\"' + ' = \'' + dic[key] + '\','
            else:
                sql_key = sql_key + '\"' + key + '\"' + ' = ' + str(dic[key]) + ' ,'


        sql =  "update {} set {} where  ".format(_tblName, sql_key[:-1]) + " \"areaId\" = '{}' ".format(ids)
        return self._pgConn.exec(sql)

    #  
    def delete(self, ids) -> bool:
        sql = "delete from {} where \"areaId\" = '{}' ".format(_tblName, ids)
        return self._pgConn.exec(sql)

    #  
    def get(self, ids) -> dict:
        sql = "select * from {} where \"areaId\" = '{}' ".format(_tblName, ids)
        return self._pgConn.exec_fetchone(sql)

    #  
    def allByCase(self, caseId) -> list:
        sql = "select * from {} where \"caseId\" = '{}'".format(_tblName, caseId)
        return self._pgConn.exec_fetchall(sql)

    def allByAdminRole1(self, adminId) -> list:
        sql = 'select * from tbl_area a left join tbl_case c on a."caseId"=c."caseId"'.format(
            adminId
        )
        return self._pgConn.exec_fetchall(sql)

    def allByAdminRole2(self, operatorId) -> list:
        sql = 'select * from tbl_area a left join tbl_case c on a."caseId"=c."caseId" WHERE c."operatorId"={}'.format(
            operatorId
        )
        return self._pgConn.exec_fetchall(sql)

    def allByAdminRole3(self, adminId) -> list:
        sql = 'select * from tbl_area a left join tbl_case c on a."caseId"=c."caseId" right join tbl_admin d on d."adminId" =  c."adminId" where d."adminId"={}'.format(
            adminId
        )
        return self._pgConn.exec_fetchall(sql)
